#!/bin/bash
#
# Outbox 事件表监控脚本
# 功能：采集 Outbox 关键指标并输出监控数据
#

set -euo pipefail

# 配置参数
MYSQL_HOST="${MYSQL_HOST:-127.0.0.1}"
MYSQL_PORT="${MYSQL_PORT:-3306}"
MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_PASSWORD="${MYSQL_PASSWORD:-ok}"
MYSQL_DB="${MYSQL_DB:-suye}"

# 监控阈值
PENDING_THRESHOLD=100
FAILED_THRESHOLD=10
MAX_RETRY_REACHED_THRESHOLD=5

# 输出 JSON 格式（便于接入监控系统）
OUTPUT_JSON=false

while [[ $# -gt 0 ]]; do
    case "$1" in
        --json)
            OUTPUT_JSON=true
            shift
            ;;
        *)
            echo "Unknown option: $1" >&2
            exit 1
            ;;
    esac
done

# 执行 MySQL 查询
query_mysql() {
    local sql="$1"
    mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" "${MYSQL_DB}" -N -e "${sql}" 2>/dev/null || echo "0"
}

# 1. Pending 事件数量
PENDING_COUNT=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE status = 0;")

# 2. Dispatching 事件数量
DISPATCHING_COUNT=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE status = 1;")

# 3. Completed 事件数量
COMPLETED_COUNT=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE status = 2;")

# 4. Failed 事件数量
FAILED_COUNT=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE status = 3;")

# 5. 重试次数达到上限的事件数
MAX_RETRY_REACHED=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE retry_count >= max_retry;")

# 6. 最老的 Pending 事件时间（延迟）
OLDEST_PENDING=$(query_mysql "SELECT COALESCE(TIMESTAMPDIFF(SECOND, MIN(created_at), NOW()), 0) FROM outbox_event WHERE status = 0;" || echo "0")

# 7. 平均重试次数
AVG_RETRY=$(query_mysql "SELECT COALESCE(ROUND(AVG(retry_count), 2), 0) FROM outbox_event WHERE status IN (0, 1, 3);" || echo "0")

# 8. 最近 1 小时完成的事件数
COMPLETED_LAST_HOUR=$(query_mysql "SELECT COUNT(*) FROM outbox_event WHERE status = 2 AND updated_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR);")

# 输出结果
if [[ "${OUTPUT_JSON}" == "true" ]]; then
    cat <<EOF
{
  "pending_count": ${PENDING_COUNT},
  "dispatching_count": ${DISPATCHING_COUNT},
  "completed_count": ${COMPLETED_COUNT},
  "failed_count": ${FAILED_COUNT},
  "max_retry_reached": ${MAX_RETRY_REACHED},
  "oldest_pending_seconds": ${OLDEST_PENDING},
  "avg_retry_count": ${AVG_RETRY},
  "completed_last_hour": ${COMPLETED_LAST_HOUR},
  "timestamp": "$(date -u +%Y-%m-%dT%H:%M:%SZ)"
}
EOF
else
    echo "=========================================="
    echo "Outbox 监控指标 - $(date)"
    echo "=========================================="
    echo ""
    echo "📊 事件状态分布："
    echo "  - Pending (待处理):    ${PENDING_COUNT}"
    echo "  - Dispatching (处理中): ${DISPATCHING_COUNT}"
    echo "  - Completed (已完成):   ${COMPLETED_COUNT}"
    echo "  - Failed (失败):        ${FAILED_COUNT}"
    echo ""
    echo "⏱️  延迟指标："
    echo "  - 最老 Pending 事件:   ${OLDEST_PENDING} 秒前创建"
    echo "  - 平均重试次数:       ${AVG_RETRY}"
    echo "  - 最近1小时完成:      ${COMPLETED_LAST_HOUR} 个"
    echo ""
    echo "⚠️  告警检查："
    
    # 检查告警条件
    ALERT=0
    if (( PENDING_COUNT > PENDING_THRESHOLD )); then
        echo "  [WARNING] Pending 事件积压过多: ${PENDING_COUNT} > ${PENDING_THRESHOLD}"
        ALERT=1
    fi
    
    if (( FAILED_COUNT > FAILED_THRESHOLD )); then
        echo "  [WARNING] Failed 事件过多: ${FAILED_COUNT} > ${FAILED_THRESHOLD}"
        ALERT=1
    fi
    
    if (( MAX_RETRY_REACHED > MAX_RETRY_REACHED_THRESHOLD )); then
        echo "  [WARNING] 达到最大重试次数的事件: ${MAX_RETRY_REACHED} > ${MAX_RETRY_REACHED_THRESHOLD}"
        ALERT=1
    fi
    
    if (( OLDEST_PENDING > 300 )); then
        echo "  [WARNING] 事件同步延迟过高: ${OLDEST_PENDING} 秒 > 300 秒"
        ALERT=1
    fi
    
    if (( ALERT == 0 )); then
        echo "  ✅ 所有指标正常"
    fi
    echo ""
fi

exit 0

